
from PyQt5.QtCore import Qt
from qfluentwidgets import InfoBarPosition, InfoBar
from src.db_utils import *
from datetime import datetime, timedelta

def get_ticket(button_id):
    driver = get_webdriver()
    wait = WebDriverWait(driver, 50)
    driver.switch_to.default_content()
    
    wait.until(EC.element_to_be_clickable((By.XPATH,'//*[@id="side-menu"]/li[2]/a'))).click()
    time.sleep(2)
    wait.until(EC.element_to_be_clickable((By.XPATH,'//span[@class="work-older-Close"]'))).click()
    time.sleep(1)
    wait.until(EC.element_to_be_clickable((By.XPATH,'//*[@id="side-menu"]/li[2]/a'))).click()
    time.sleep(5)

    iframe2 = wait.until(EC.visibility_of_element_located((By.XPATH,'/html/body/div[3]/div[1]/div[4]/iframe[2]')))
    driver.switch_to.frame(iframe2)
    time.sleep(2)

    lis = WebDriverWait(driver, 10).until(
        EC.presence_of_all_elements_located((
            By.XPATH,
            "//div[@class='worker_left_middle']//ul/li"
        ))
    )
    for li in lis:
        try:
            header = li.find_element(By.XPATH, "./div[contains(@class,'ticket-title-content')]")
            driver.execute_script("arguments[0].scrollIntoView();", header)  # 防遮挡
            header.click()
        except Exception:
            continue
    wait.until(EC.element_to_be_clickable((By.XPATH,'//li[contains(@class, "active") and .//div[@title="本人相关工单和工作记录"]]/dl[2]/dd/span'))).click()
    time.sleep(5)
    wait.until(EC.element_to_be_clickable((By.XPATH,'//li[contains(@class, "active") and .//div[@title="本人相关工单和工作记录"]]/dl[2]/dd/span'))).click()
    time.sleep(5)
    if button_id == "get_ticket_content25":
        wait.until(EC.element_to_be_clickable((By.XPATH,'//div[@class="fixed-table-pagination"]/div/span[2]/span/button'))).click()
        wait.until(EC.element_to_be_clickable((By.XPATH,'//div[@class="fixed-table-pagination"]/div/span[2]/span/ul/li[2]/a'))).click()
        time.sleep(15)
    elif button_id == "get_ticket_content50":
        wait.until(EC.element_to_be_clickable((By.XPATH,'//div[@class="fixed-table-pagination"]/div/span[2]/span/button'))).click()
        wait.until(EC.element_to_be_clickable((By.XPATH,'//div[@class="fixed-table-pagination"]/div/span[2]/span/ul/li[3]/a'))).click()
        time.sleep(20)
    elif button_id == "get_ticket_content100":
        wait.until(EC.element_to_be_clickable((By.XPATH,'//div[@class="fixed-table-pagination"]/div/span[2]/span/button'))).click()
        wait.until(EC.element_to_be_clickable((By.XPATH,'//div[@class="fixed-table-pagination"]/div/span[2]/span/ul/li[4]/a'))).click()
        time.sleep(20)

    # 定位表格元素
    table_data = []
    table = wait.until(EC.visibility_of_element_located((By.XPATH, '//table[@id="ticketTable"]/tbody')))
    
    # 批量获取表格行数据
    rows = table.find_elements(By.TAG_NAME, 'tr')  # 获取所有行元素
    for row in rows:
        row_data = []
        cells = row.find_elements(By.TAG_NAME, 'td')  # 获取当前行的所有单元格元素
        for cell in cells:
            cell_data = cell.text  # 获取单元格文本内容
            row_data.append(cell_data)  # 将单元格数据添加到行数据列表中
        table_data.append(row_data)  # 将行数据添加到表格数据列表中

    db = Database()
    c = db.get_connection()
    db.backup_db()
    current_time = datetime.now()
    three_month_ago = current_time - timedelta(days=90)
    three_month_ago_str = three_month_ago.strftime("%Y-%m-%d %H:%M:%S")

    ticket_query = ("SELECT id, custom_name, custom_phone, serial_num,"
        "custom_company, custom_department,"
        "custom_default_area, ticket_title,"
        "ticket_code, question_ask, question_done,"
        "use_time, start_time, end_time, is_import,"
        "is_local, ticket_status, service_level, contract_id,"
        "product_id, l2_name, ticket_area "
        "FROM ticket WHERE ticket_status IS NOT NULL and start_time > ?")
    c.execute(ticket_query, (three_month_ago_str,))
    
    ticket_sheet = c.fetchone()
    while ticket_sheet:
        for row_data in table_data:
            column_ticket_code = row_data[2]        #工单编码--495925--ticket_code
            column_ticket_status = row_data[3]      #工单状态--已解决--类似ticket_status=4
            column_status = row_data[4]             #工单综合服务节点--满调中--column_status
            column_ticket_title = row_data[5]       #工单标题ticket_title
            column_service_level = row_data[6]      #服务级别--P4-低--service_level
            column_is_local = row_data[7]           #服务方式-远程支持-现场支持--is_local
            column_localstatus = row_data[8]        #服务标识-远程单现场单
            column_service_type = row_data[9]       #服务类型区分--标准服务--超合同服务
            column_service_name = row_data[10]      #服务产品名称--标准增值5*8
            column_l1_namedetail = row_data[11]     #L1处理人--l1名--
            column_l1_namekefu = row_data[12]       #客服姓名
            column_start_time = row_data[15]        #创建时间start_name
            custom_default_area = row_data[16]      #区域属性--西藏--ticket_area
            column_local_start_time = row_data[17]  #本地开始时间
            custom_department = row_data[18]        #公司名
            column_company = row_data[18]           #工单公司custom_company
            column_name = row_data[19]              #联系人姓名custom_name
            custom_phone = row_data[20]             #联系人电话custom_phone
            column_serial_num = row_data[21]        #服务序列号serial_num
            column_product_name = row_data[22]      #服务产品名称--标准增值5*8
            column_qask= row_data[23]               #工单问题question_ask
            column_fuwutai_name = row_data[25]      #服务台受理人
            column_qdon= row_data[26]               #工单l1结论question_done
            column_l2_name = row_data[27]           #l2名字：l2_name
            column_l2_done = row_data[28]           #l2结论：l2_done
            column_use_time = row_data[32]          #工单使用时间-
            column_product_id = row_data[33]        #项目编号product_id
            column_contract_id = row_data[34]       #合同编号contract_id
            column_local_end_time = row_data[35]    #服务解决时间
            column_end_time = row_data[35]          #结束时间end_name
            column_phase = row_data[36]             #支持状态ticket_phase-售前支持/售后支持

            default_name = db.get_default_set_data('default_name')
            phone = db.get_default_set_data('custom_phone')
            column_is_del = ''
            if column_fuwutai_name:
                # 按逗号分割字符串，得到名字和编号的列表
                l1_names = [item.split('-')[0] for item in column_l1_namedetail.split(',')]
                fuwutai_names = [item.split('-')[0] for item in column_fuwutai_name.split(',')]

                # 检查 default_name 是否在 fuwutai_names 中，但不在 l1_names 中

                if default_name in fuwutai_names and default_name not in l1_names:
                    column_is_del = '1'
                else:
                    column_is_del = ''

            print(column_is_del)

            # 检查本地数据库中是否已存在此工单编号，存在则更新
            c.execute("SELECT COUNT(*), id FROM ticket WHERE ticket_code = ? ", (column_ticket_code,))
            result = c.fetchone()
            if result[0] > 0:
                # 情况1：存在相同ticket_code，更新ticket和对应的ticket_time
                ticket_id = result[1] 
                if ticket_id is None:
                    print(f"ticket_code {column_ticket_code} 对应的 id 为空，跳过 ticket_time 更新")
                else:
                    # 更新ticket表
                    c.execute("""
                        UPDATE ticket 
                        SET custom_phone = ?, serial_num = ?, custom_department = ?, column_ticket_title = ?, 
                            contract_id = ?, product_id = ?, l2_name = ?, column_status = ?, l2_done = ?, 
                            local_start_time = ?, local_end_time = ?, is_del = ? ,ticket_phase = ? ,column_service_type = ?,column_ticket_status = ? 
                        WHERE ticket_code = ?
                    """, (
                        custom_phone, column_serial_num, custom_department, column_ticket_title,
                        column_contract_id, column_product_id, column_l2_name, column_status, column_l2_done,
                        column_start_time, column_local_end_time, column_is_del, column_phase,column_service_type,column_ticket_status, column_ticket_code
                    ))
                    # 更新ticket_time表的ticket_code
                    c.execute("UPDATE ticket_time SET ticket_code = ? WHERE ticket_id = ? ", (column_ticket_code, ticket_id))
                    print(f"已更新工单编号为 {column_ticket_code},标题为：'{column_ticket_title}' 的工单数据")
            else:
                # 情况2：不存在相同ticket_code，先按【serial_num + 标题】匹配（优先级1）
                c.execute("""
                    SELECT COUNT(*), id 
                    FROM ticket 
                    WHERE serial_num = ?  -- 原始serial_num匹配
                    AND ? LIKE '%' || ticket_title || '%'  -- 标题包含匹配
                """, (column_serial_num, column_ticket_title))  # 参数：原始serial_num和标题
                serial_result = c.fetchone()
                
                if serial_result[0] > 0:
                    # 2.1：serial_num + 标题匹配到工单，更新数据
                    ticket_id = serial_result[1]
                    if ticket_id is None:
                        print(f"序列号 {column_serial_num} 对应的id为空，跳过ticket_time更新")
                    else:
                        print(f"通过序列号 {column_serial_num} + 标题匹配到工单，更新数据")
                        # 更新ticket表
                        c.execute("""
                            UPDATE ticket 
                            SET ticket_code = ?, column_ticket_title = ?, contract_id = ?, product_id = ?, 
                                l2_name = ?, column_status = ?, l2_done = ?, local_start_time = ?, 
                                local_end_time = ?, is_del = ?, ticket_phase = ?,column_service_type = ?,column_ticket_status = ? 
                            WHERE serial_num = ? AND ? LIKE '%' || ticket_title || '%'
                        """, (
                            column_ticket_code, column_ticket_title, column_contract_id, column_product_id,
                            column_l2_name, column_status, column_l2_done, column_start_time,
                            column_local_end_time, column_is_del , column_phase, column_service_type, column_ticket_status,
                            column_serial_num, column_ticket_title  # 条件参数：原始serial_num和标题
                        ))
                        # 更新ticket_time的ticket_code
                        c.execute("UPDATE ticket_time SET ticket_code = ? WHERE ticket_id = ? ", (column_ticket_code, ticket_id))
                
                else:
                    # 2.2：serial_num + 标题未匹配到，再按【column_product_id + 标题】匹配（优先级2）
                    c.execute("""
                        SELECT COUNT(*), id 
                        FROM ticket 
                        WHERE serial_num = ?  -- 用column_product_id作为serial_num条件
                        AND ? LIKE '%' || ticket_title || '%'  -- 标题包含匹配
                    """, (column_product_id, column_ticket_title))  # 参数：column_product_id和标题
                    product_result = c.fetchone()
                    
                    if product_result[0] > 0:
                        # 2.2.1：column_product_id + 标题匹配到工单，更新数据
                        ticket_id = product_result[1]
                        if ticket_id is None:
                            print(f"column_product_id {column_product_id} 对应的id为空，跳过ticket_time更新")
                        else:
                            print(f"通过column_product_id {column_product_id} + 标题匹配到工单，更新数据")
                            # 更新ticket表
                            c.execute("""
                                UPDATE ticket 
                                SET ticket_code = ?, custom_phone = ?, serial_num = ?, custom_department = ?, 
                                    column_ticket_title = ?, contract_id = ?, l2_name = ?, column_status = ?, 
                                    l2_done = ?, local_start_time = ?, local_end_time = ?, is_del = ?,ticket_phase = ? ,column_service_type = ?,column_ticket_status = ? 
                                WHERE serial_num = ? AND ? LIKE '%' || ticket_title || '%'
                            """, (
                                column_ticket_code, custom_phone, column_serial_num, custom_department,
                                column_ticket_title, column_contract_id, column_l2_name, column_status,
                                column_l2_done, column_start_time, column_local_end_time, column_is_del, column_phase,column_service_type,column_ticket_status,
                                column_product_id, column_ticket_title  # 条件参数：column_product_id和标题
                            ))
                            # 更新ticket_time的ticket_code
                            c.execute("UPDATE ticket_time SET ticket_code = ? WHERE ticket_id = ? ", (column_ticket_code, ticket_id))
                    
                    else:
                        # 2.2.2：所有匹配失败，新增工单并插入ticket_time
                        print(f"无匹配工单，新增序列号 {column_serial_num}（标题：'{column_ticket_title}'）")
                        # 插入ticket表
                        c.execute("""
                            INSERT INTO ticket (
                                custom_name, custom_phone, serial_num, custom_company, custom_department, 
                                custom_default_area, ticket_title, column_ticket_title, ticket_code, 
                                question_ask, question_done, start_time, end_time, is_import, is_local, 
                                ticket_status, service_level, contract_id, product_id, l2_name, ticket_area, 
                                ticket_phase, product_name, is_worktime, column_status, l2_done, is_update, 
                                is_del, will_l2, is_centos, local_start_time, local_end_time,column_service_type ,column_ticket_status
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        """, (
                            column_name, custom_phone, column_serial_num, column_company, custom_department,
                            custom_default_area, column_ticket_title, column_ticket_title, column_ticket_code,
                            column_qask, column_qdon, column_start_time, column_local_end_time, '1', column_is_local,
                            4, column_service_level, column_contract_id, column_product_id, column_l2_name,
                            custom_default_area, column_phase, column_product_name, '否', column_status,
                            column_l2_done, '否', column_is_del, '否', '否', column_local_start_time, column_local_end_time,column_service_type,column_ticket_status
                        ))
                        if column_is_del == "1":
                            print("这条不是我的工单，不插入工时")
                        else:
                        # 获取新工单id并插入ticket_time
                            new_ticket_id = c.lastrowid
                            if new_ticket_id is not None:
                                dept_value = custom_department if custom_department is not None else column_company
                                c.execute("""
                                    INSERT INTO ticket_time (
                                        is_worktime, work_contect, use_time, start_time, end_time, ticket_id, ticket_code, custom_department,is_del
                                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                                """, (
                                    '工单', f"{column_qask}---{column_qdon}",column_use_time, column_start_time, column_local_end_time,
                                    new_ticket_id, column_ticket_code, dept_value,'1'
                                ))
                            else:
                                print("新增工单失败，未获取到ticket_id，无法插入ticket_time")
                # 添加用户
                c.execute("SELECT COUNT(*) FROM integrator where serial_num = ? and custom_phone = ? ", (column_serial_num, custom_phone))
                result1 = c.fetchone()
                if result1[0] == 0:
                    c.execute("INSERT INTO integrator (custom_name,serial_num,custom_phone,custom_department,custom_company,custom_default_area,industry) VALUES (?, ?, ?, ?, ?, ?, ?)", 
                            (column_name,column_serial_num, custom_phone, column_company, column_company,custom_default_area,column_service_name))
                            
        ticket_sheet = c.fetchone()
    db.close_connection()
    
    driver.switch_to.default_content()
    wait.until(EC.element_to_be_clickable((By.XPATH,'//span[@class="work-older-Close"]'))).click()
    time.sleep(1)
    print("ok_close")